/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package model;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.util.Map;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
/**
 *
 * @author Eduard
 */
public class PuntuacioBD {
    private QueryRunner run;
    private MapListHandler mlh;
    private MapHandler mh;

    public PuntuacioBD() throws NamingException, SQLException {
            Context ctx = new InitialContext();
            DataSource odsconn = (DataSource)ctx.lookup("jdbc/volansbd");
            run = new QueryRunner(odsconn);
            mlh = new MapListHandler();
            mh = new MapHandler();
    }

    
    public float consultaPuntuacio(int id) throws SQLException {
        try {
            return ((BigDecimal) run.query("select AVG(puntuacio) from puntuacio where id_prod=?", mh, id).get("AVG(puntuacio)")).floatValue();
        } catch (NullPointerException e) {
            return -1;
        }
    }

    public int consultaPuntuacio(int idClient, int idProd) throws SQLException {
        try {
            return ((BigDecimal) run.query("select puntuacio from puntuacio where id_client=? and id_prod=?", mh, idClient, idProd).get("puntuacio")).intValue();
        } catch (NullPointerException e) {
            return -1;
        }
    }


    public boolean introdueixPuntuacio(int id_prod, int punt, int idClient) throws SQLException {
        Map<String, Object> sel = run.query("select puntuacio from puntuacio where id_client=? and id_prod=?", mh, idClient, id_prod);
        String consulta = "INSERT INTO \"IG21254\".\"PUNTUACIO\" (ID_PUNT, PUNTUACIO, id_client, ID_PROD) VALUES (idpunt.nextval, ?, ?, ?)";
        if (sel != null && !sel.isEmpty()) {
            consulta = "UPDATE puntuacio SET puntuacio=? WHERE id_client=? and id_prod=?";
        }
        int num = run.update(consulta, punt, idClient, id_prod);
        if (num == 0) {
            return false;
        }
        return true;
    }
}
